﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using MYTech.Data;
using MYTech.Data.Oracle;

namespace MYTech.Oracle
{
    /// <summary>
    /// Oracle相关工具类
    /// </summary>
    public class Utils
    {
        /// <summary>
        /// 获取字段属性
        /// </summary>
        /// <param name="property">属性</param>
        /// <param name="fieldType">字段类型</param>
        /// <param name="precision">精度</param>
        /// <param name="scale">小数位</param>
        public static void GetProperty(string property, ref FieldType fieldType, ref int precision, ref int scale)
        {
            int index = property.IndexOf("(");
            string type = null;
            if (index > 0)
            {
                type = property.Substring(0, index);
                int end = property.LastIndexOf(")");
                if (end - index > 0)
                {
                    string val = property.Substring(index + 1, end - index - 1);
                    int _index = val.IndexOf(",");
                    if (_index > 0)
                    {
                        precision = Convert.ToInt32(val.Substring(0, _index));
                        scale = Convert.ToInt32(val.Substring(_index + 1));
                    }
                    else
                    {
                        precision = Convert.ToInt32(val);
                        scale = -1;
                    }
                }
            }
            else
                type = property;
            foreach (FieldType _type in Enum.GetValues(typeof(FieldType)))
            {
                if (_type.ToString().ToUpper().Equals(type))
                {
                    fieldType = _type;
                    break;
                }
            }
        }

        /// <summary>
        /// 获取各属性字段数组
        /// </summary>
        /// <param name="propertyFields">字段属性</param>
        /// <returns></returns>
        public static string[] GetPropertyFields(string propertyFields)
        {
            MatchCollection _fields = Regex.Matches(propertyFields, @"[A-Za-z_][A-Za-z0-9_\s]+[A-Za-z][A-Za-z0-9\s]+(\([0-9,]+\))?(\s+DEFAULT)?(\s+[0-9]+|\'[A-Za-z0-9]+\')?(\s+NOT\s+NULL)?", RegexOptions.None);//[A-Za-z][A-Za-z0-9]+\s+[A-Za-z][A-Za-z0-9]+(\([0-9]+\)|\([0-9]+,[0-9]+\))?(\s+DEFAULT)?(\s+[0-9]+|\'[A-Za-z0-9]+\')?(\s+NOT\s+NULL)?
            string[] fields = new string[_fields.Count];
            for (int i = 0; i < _fields.Count; i++)
            {
                fields[i] = _fields[i].Value;
            }
            return fields;
        }

        /// <summary>
        /// 根据字段信息获取字段属性字符串
        /// </summary>
        /// <param name="fields">字段数组</param>
        /// <returns></returns>
        public static string GetPropertyFields(Field[] fields)
        {
            if (fields != null && fields.Length > 0)
            {
                List<string> _items = new List<string>();
                foreach (Field field in fields)
                {
                    _items.Add(field.Expression);
                }
                return string.Join(",", _items.ToArray());
            }
            return null;
        }

        /// <summary>
        /// 获取各字段
        /// </summary>
        /// <param name="propertyFields">字段属性</param>
        /// <returns></returns>
        public static Field[] GetFields(string propertyFields)
        {
            string[] fields = GetPropertyFields(propertyFields);
            Field[] _fields = new Field[fields.Length];
            for (int index = 0; index < fields.Length; index++)
            {
                _fields[index] = new Field(fields[index]);
            }
            return _fields;
        }

        public static Table AnalyzeCreateSql(string sql, bool init)
        {
            if (!string.IsNullOrEmpty(sql))
            {
                char[] chars = sql.ToUpper().Trim().ToCharArray();
                if (chars.Length == 0)
                    return null;
                string fields = null;
                string partitonBy = null;
                StringBuilder temp = new StringBuilder();
                StringBuilder tempFields = new StringBuilder();
                string prevTemp = null;
                bool isSpecial = false;
                char _charVal = '\0';
                int status = 0;
                Table table = null;
                for (int index = 0; index < chars.Length; index++)
                {
                    char _val = chars[index];
                    if (!specialChars.Contains(_val) && table == null)
                    {
                        temp.Append(_val);
                        isSpecial = false;
                    }
                    else
                    {
                        if (isSpecial || _charVal != _val)
                            _charVal = _val;
                        isSpecial = true;
                    }
                    if (isSpecial)
                    {
                        string val = temp.ToString();
                        if (table == null)
                        {
                            temp = new StringBuilder();
                            if (prevTemp != null && prevTemp.Equals("TABLE"))
                                table = new Table(val);
                        }
                        else
                        {
                            tempFields.Append(_val);
                            if (table.Fields == null || table.PartitionBy == null)
                            {
                                string tempStr = tempFields.ToString();
                                if (_val == '(')
                                    status = (status << 1) + 1;
                                else if (_val == ')')
                                    status = (status - 1) >> 1;
                                if (status == 0 && fields == null && table.Fields == null)
                                {
                                    fields = tempStr;
                                    table.Fields = GetFields(fields);
                                    tempFields = new StringBuilder();
                                }
                                else if (tempStr.Contains("PARTITION") && tempStr.Contains("BY") && tempStr.Contains("RANGE"))
                                {
                                    Match match = Regex.Match(tempStr, @"\([A-Z0-9]+\)");
                                    if (match.Success)
                                    {
                                        tempFields = new StringBuilder();
                                        partitonBy = match.Value.Replace("(", "").Replace(")", "");
                                        table.PartitionBy = partitonBy;
                                    }
                                }
                            }
                        }
                        if (!string.IsNullOrEmpty(val))
                        {
                            prevTemp = val;
                        }
                    }
                }
                return table;
            }
            return null;
        }

        /// <summary>
        /// 分析建表语句
        /// </summary>
        /// <param name="sql">建表语句</param>
        /// <returns></returns>
        public static Table AnalyzeCreateSql(string sql)
        {
            if (!string.IsNullOrEmpty(sql))
            {
                char[] chars = sql.ToUpper().Trim().ToCharArray();
                if (chars.Length == 0)
                    return null;
                string fields = null;
                string partitonBy = null;
                StringBuilder temp = new StringBuilder();
                string prevTemp = null;
                bool isSpecial = false;
                char _charVal = '\0';
                Fragment frag = new Fragment();
                Table table = null;
                for (int index = 0; index < chars.Length; index++)
                {
                    if (!specialChars.Contains(chars[index]) && table == null)
                    {
                        temp.Append(chars[index]);
                        isSpecial = false;
                    }
                    else
                    {
                        if (isSpecial || _charVal != chars[index])
                            _charVal = chars[index];
                        isSpecial = true;
                    }
                    if (isSpecial)
                    {
                        string val = temp.ToString();
                        if (table == null)
                        {
                            temp = new StringBuilder();
                            if (prevTemp != null && prevTemp.Equals("TABLE"))
                                table = new Table(val);
                        }
                        else
                        {
                            if (!frag.IsEnd)
                                frag.Add(chars[index]);
                            if (frag.IsEnd)
                            {
                                string _val = frag.ToString();
                                if (fields == null)
                                {
                                    fields = _val;
                                    table.Fields = GetFields(fields);
                                }
                                else if (_val.Contains("PARTITION") && _val.Contains("BY") && _val.Contains("RANGE"))
                                {
                                    Match match = Regex.Match(_val, @"\([A-Z0-9]+\)");
                                    if (match.Success)
                                    {
                                        partitonBy = match.Value.Replace("(", "").Replace(")", "");
                                        table.PartitionBy = partitonBy;
                                    }
                                }
                                frag = new Fragment();
                            }
                        }
                        if (!string.IsNullOrEmpty(val))
                        {
                            prevTemp = val;
                        }
                    }
                }
                return table;
            }
            return null;
        }

        #region
        class Fragment
        {
            private StringBuilder sb;
            public Fragment()
            {
                sb = new StringBuilder();
            }

            public override string ToString()
            {
                return sb.ToString();
            }

            private bool isSpecial = false;
            private bool isEnd1 = false;
            private bool isEnd2 = false;
            private bool isStart1 = false;
            private bool isStart2 = false;
            private bool isTemp = true;
            public bool IsEnd = false;
            public void Add(char val)
            {
                if (!IsEnd)
                    sb.Append(val);
                if (val == '(' && !isStart1)
                    isStart1 = true;
                else if (val == '(' && isStart1)
                {
                    isTemp = false;
                    isStart2 = true;
                }
                else if (val == ')' && isStart2 && !isEnd2)
                {
                    isTemp = true;
                    isEnd2 = true;
                }
                else if (val == ')' && isTemp)
                    isEnd1 = true;
                if (isTemp)
                {
                    isStart2 = false;
                    isEnd2 = false;
                }
                if ((isStart1 && isEnd1) || (isStart1 && isEnd1 && isTemp))
                {
                    IsEnd = true;
                }
            }
        }
        #endregion

        /// <summary>
        /// 根据表信息获取建表语句
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public static string GetCreateSql(Table table)
        {
            if (table != null)
                return string.Format("create table {0}({1})", table.TableName, table.PropertyFields);
            return null;
        }

        private static List<char> specialChars = new List<char> { ',', ' ', '=', '>', '<', '/', '(', ')', '+', '-', '\n', '\r' };
        /// <summary>
        /// 去除前额外的空格
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private static string RemovePrevExtraSpaces(string sql)
        {
            char[] chars = sql.Trim().ToCharArray();
            char c = chars[0];
            StringBuilder str = new StringBuilder();
            str.Append(c);
            for (int i = 1; i < chars.Length; i++)
            {
                if (chars[i].Equals(' '))
                {
                    if (!specialChars.Contains(c))
                    {
                        str.Append(chars[i]);
                        c = chars[i];
                    }
                }
                else
                {
                    str.Append(chars[i]);
                    c = chars[i];
                }
            }
            return str.ToString();
        }

        /// <summary>
        /// 去除额外的空格
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string RemoveExtraSpaces(string sql)
        {
            string temp = RemovePrevExtraSpaces(sql);
            string midStr = StringHelper.Reverse(temp);
            return StringHelper.Reverse(RemovePrevExtraSpaces(midStr));
        }

        public static Table GetOracleTable(string tableName)
        {
            try
            {
                Table table = new Table() { TableName = tableName };
                List<Field> fields = new List<Field>();
                DataHelper.ExecuteNonQuery(string.Format("select COLUMN_ID,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,DATA_DEFAULT FROM USER_TAB_COLUMNS T WHERE t.TABLE_NAME='{0}'", tableName), dataReader =>
                {
                    int length = 0;
                    string type = dataReader[2].ToString();
                    FieldType tempType = FieldType.None;
                    int index = type.IndexOf('(');
                    foreach (FieldType _type in Enum.GetValues(typeof(FieldType)))
                    {
                        string _temp = type;
                        if (index >= 0)
                            _temp = type.Substring(0, index);
                        if (_type.ToString().ToUpper().Equals(_temp))
                        {
                            tempType = _type;
                            break;
                        }
                    }
                    Field field = new Field() { IndexID = Convert.ToInt32(dataReader[0].ToString()), FieldName = dataReader[1].ToString(), Type = tempType, Precision = length > 0 ? length : (dataReader[4] != DBNull.Value && !string.IsNullOrEmpty(dataReader[4].ToString())) ? Convert.ToInt32(dataReader[4].ToString()) : Convert.ToInt32(dataReader[3].ToString()), Scale = length > 0 ? -1 : !string.IsNullOrEmpty(dataReader[5].ToString()) ? Convert.ToInt32(dataReader[5].ToString()) : -1, NullAble = dataReader[6].ToString().Equals("Y") ? true : false, Default = dataReader[7] };
                    fields.Add(field);
                    return true;
                });
                table.Fields = fields.ToArray();
                return table;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static TableCollection<Table> GetOracleTables(TableCollection<Table> tables)
        {
            if (tables == null || tables.Count == 0)
                return null;
            if (tables.Count == 1)
                return new TableCollection<Table>() { GetOracleTable(tables[0].TableName) };
            TableCollection<Table> ret = new TableCollection<Table>();
            Dictionary<string, List<Field>> _table = new Dictionary<string, List<Field>>();
            DataHelper.ExecuteNonQuery("select TABLE_NAME,COLUMN_ID,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,DATA_DEFAULT FROM USER_TAB_COLUMNS T", dataReader =>
            {
                string tableName = dataReader[0].ToString();
                int length = 0;
                string type = dataReader[3].ToString();
                FieldType tempType = FieldType.None;
                int index = type.IndexOf('(');
                foreach (FieldType _type in Enum.GetValues(typeof(FieldType)))
                {
                    string _temp = type;
                    if (index >= 0)
                        _temp = type.Substring(0, index);
                    if (_type.ToString().ToUpper().Equals(_temp))
                    {
                        tempType = _type;
                        break;
                    }
                }
                Field field = new Field() { IndexID = Convert.ToInt32(dataReader[1].ToString()), FieldName = dataReader[2].ToString(), Type = tempType, Precision = length > 0 ? length : (dataReader[5] != DBNull.Value && !string.IsNullOrEmpty(dataReader[5].ToString())) ? Convert.ToInt32(dataReader[5].ToString()) : Convert.ToInt32(dataReader[4].ToString()), Scale = length > 0 ? -1 : !string.IsNullOrEmpty(dataReader[6].ToString()) ? Convert.ToInt32(dataReader[6].ToString()) : -1, NullAble = dataReader[7].ToString().Equals("Y") ? true : false, Default = dataReader[8] };
                if (!_table.ContainsKey(tableName))
                    _table.Add(tableName, new List<Field>() { field });
                else
                    _table[tableName].Add(field);
                return true;
            });
            foreach (Table table in tables)
            {
                List<Field> fields = new List<Field>();
                if (_table.TryGetValue(table.TableName, out fields))
                {
                    ret.Add(new Table() { TableName = table.TableName, Fields = fields.ToArray() });
                }
                else
                    ret.Add(new Table(table.TableName));
            }
            return ret;
        }

        public static List<TablePartition> GetTablePartitions()
        {
            Dictionary<string, TablePartition> tablePartitions = new Dictionary<string, TablePartition>();
            DataHelper.ExecuteNonQuery("SELECT U.TABLE_NAME,U.PARTITIONED,P.PARTITION_NAME FROM USER_TABLES U LEFT JOIN USER_TAB_PARTITIONS P ON U.TABLE_NAME=P.TABLE_NAME", reader =>
            {
                string tableName = reader[0].ToString();
                bool isPartitioned = reader[1].ToString().Equals("YES");
                string partitionName = reader[2].ToString();
                TablePartition partition = new TablePartition(tableName, isPartitioned);
                if (!tablePartitions.ContainsKey(tableName))
                    tablePartitions.Add(tableName, partition.AddPartition(partitionName));
                else if (isPartitioned)
                    tablePartitions[tableName].AddPartition(partitionName);
                return true;
            });
            List<TablePartition> partitions = new List<TablePartition>();
            foreach (KeyValuePair<string, TablePartition> partition in tablePartitions)
            {
                partition.Value.Analyze();
                partitions.Add(partition.Value);
            }
            return partitions;
        }
    }
}
